package edu.yuhf.jdbcpart;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import edu.yuhf.dbutil.DBConnection;
import edu.yuhf.dbutil.JdbcTemplate;
import edu.yuhf.domain.User;

public class UserTableOperation {
	
	//查询数据库users表中所有的内容
	public List<User> queryAll(){
		String sql="select *from users";
		List<User> list=new ArrayList<>();
		Connection conn=DBConnection.getConnection();
		try {
			PreparedStatement psmt=conn.prepareStatement(sql);
			ResultSet rs=psmt.executeQuery();
			while(rs.next()) {
				User user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
				list.add(user);
			}
		} catch (SQLException e) {
			System.out.println(UserTableOperation.class.getName()+": select * from users method error,message is "+e.getMessage());
		}
		return list;
	}
	
	//根据id查询数据库整条user
	public User querById(int id) {
		String sql="select *from users where id=?";
		User user=null;
		
		Connection conn=DBConnection.getConnection();
		
		try {
			PreparedStatement psmt=conn.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs=psmt.executeQuery();
			if(rs.next()) {
				user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
			}
		} catch (SQLException e) {
			System.out.println(UserTableOperation.class.getName()+": query By Id method error,message is "+e.getMessage());
		}
		
		
		return user;
	}
	
	//根据name查询数据库整条user
	public User querByName(String name) {
		User user=null;
		String sql="select * from users where name=?";
		Connection conn=DBConnection.getConnection();
		try {
			PreparedStatement psmt=conn.prepareStatement(sql);
			psmt.setString(1, name);
			ResultSet rs=psmt.executeQuery();
			if(rs.next()) {
				user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
			}
		} catch (SQLException e) {
			System.out.println(UserTableOperation.class.getName()+": query By Name method error,message is "+e.getMessage());
		}
		return user;
	}
	
	//数据库中添加一条新的user
	public int addUser(User user) {
		
		String sql="insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
		return JdbcTemplate.update(sql, user);
		
	}
	
	//根据id的删除操作
	public int deleteUser(int id) {
		int rowNum=0;
		String sql="delete from users where id=?";
		Connection conn=DBConnection.getConnection();
		try {
			PreparedStatement psmt=conn.prepareStatement(sql);
			psmt.setInt(1, id);
			
			rowNum=psmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println(UserTableOperation.class.getName()+": delete users by id method error,message is "+e.getMessage());
		}
		
		return rowNum;
	}
	
	//根据name的删除操作
	public int deleteUser(String name) {
		int rowNum=0;
		String sql="delete from users where name=?";
		Connection conn=DBConnection.getConnection();
		try {
			PreparedStatement psmt=conn.prepareStatement(sql);
			psmt.setString(1, name);
			
			rowNum=psmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println(UserTableOperation.class.getName()+": delete users by name method error,message is "+e.getMessage());
		}
		
		return rowNum;
			
	}
	//修改操作 传入sql语句和一个不定长参数
	public int updateUser(String sql,Object...param) {
		
		return JdbcTemplate.update(sql, param);
	}
	
	
	
}
